<?php
Class CNVDAO
{
	public $connexion;

	public function __construct()
	{
		try {
			$this->connexion = new PDO("mysql:host=".SERVEUR_MYSQL.";dbname=".BASE.";charset=utf8", UTILISATEUR, M2P);
		}
		catch(PDOException $e)
		{
			echo "Erreur : ".$e->getMessage();
			die();
		}
	}
	
// recuperer_cytobande_par_cnv

	public function recuperer_cyto_par_idCNV($idCNV)
	{
	
		include ("connexion2.php");
		$id_cnv = $idCNV;

			// Requête permettant de récupérer les coordonnées du cnv en version HG19
		$ReqAttri = "select a.attribute_value, b.tag from base_hg.hg18_attribute AS a, base_hg.hg18_attributelist AS b where a.attribute_id = b.id and a.id = ".$id_cnv;

		if ($result2 = mysql_query($ReqAttri)){
				while($row2 = mysql_fetch_object($result2)) {
					if ($row2->tag == 'Cytoband'){
					$Cytob = $row2->attribute_value;
					}
				}
				
		}else{
				mysql_error();
		}



				$arrayCyto = explode(',',$Cytob, -1);
				$taille_tab = count($arrayCyto);
				$cyt="";
				for($i=0; $i<$taille_tab; $i++){
				$cyt .= $arrayCyto[$i];
				if($i+1 < $taille_tab) $cyt .= ", ";
				}
			
	return $cyt;
	
}

// recuperer_interval_hg19_correspond_hg18_par_cnv	
	public function recuperer_INTER_par_idCNV($idCNV)
	{
	
		include ("connexion2.php");
		$id_cnv = "CNV_".$idCNV."0";

			// Requête permettant de récupérer les coordonnées du cnv en version HG19
			$ReqCoordHg19 = "select start, end from base_hg.hg19_feature where id = (select id from base_hg.hg19_name where name like '".$id_cnv."')";
			$result3 = mysql_query($ReqCoordHg19);
			$row3 = mysql_fetch_object($result3);
			$startHg19 = $row3->start;
			$endHg19 = $row3->end;
			$loca_cnv_hg19=$startHg19."..".$endHg19;
			return $loca_cnv_hg19;
	}


// recuperer_cytobande_par_CNV

	public function recuperer_CNV_par_puce($idpuce)
	{
		$query = "SELECT  idpuce, idcnv, positiondebut, positionfin, chromosome, sondedebut, sondefin, log2ratio, nbcopie ,verifie, methodeverification, herite, commentaire, classification "
				." FROM cnv_hg18";
		$query .=" WHERE 1 ";
		if ($idpuce != null && $idpuce != 0){
			$query .= " AND idpuce = :idpuce";
		}
		$requette_preparee = $this->connexion->prepare($query);
		if ($idpuce != null){
			$requette_preparee->bindValue(":idpuce" , $idpuce, PDO::PARAM_INT);
		}
		$requette_preparee->execute();
		$cnv = $requette_preparee->fetchAll(PDO::FETCH_CLASS,'CNV');
		return  $cnv;
	
	} // 

	public function recuperer_CNV_par_idCNV($idCNV)
	{
		$query = "SELECT  idpuce, idcnv, positiondebut, positionfin, chromosome, sondedebut, sondefin, log2ratio, nbcopie ,verifie, methodeverification, herite, commentaire, classification "
				." FROM cnv_hg18";
		$query .=" WHERE 1 ";
		if ($idCNV != null && $idCNV != 0){
			$query .= " AND idcnv = :idcnv";
		}
		$requette_preparee = $this->connexion->prepare($query);
		if ($idCNV != null && $idCNV != 0){
			$requette_preparee->bindValue(":idcnv" , $idCNV, PDO::PARAM_INT);
		}
		$requette_preparee->execute();
		$cnv = $requette_preparee->fetchAll(PDO::FETCH_CLASS,'CNV');
		return  $cnv[0];
	
	} // recuperer_CNV_par_idCNV
	
////////////////////////////////////////////
	public function ajouter_cnv($cnv)
	{


		$message = null;
////////////////////////////////////////////
		$ctrl=-1;
		$puce = $cnv->idpuce;
		$baseB  = $cnv->positiondebut;
		$baseH  = $cnv->positionfin;

		
		$chro = $cnv->chromosome;
		if ($cnv->sondedebut != null){
		    $sondedebut = $cnv->sondedebut;
		}
		else{
		$sondedebut = 0;
		}
		if ($cnv->sondefin != null){
		$sondefin = $cnv->sondefin;
		}
		else{
		$sondefin = 0;
		}

		$log2ratio = $cnv->log2ratio;
		$nbcopie = $cnv->nbcopie;

		$verifie = $cnv->verifie; 
		
		$methodeverification = $cnv->methodeverification;
  
		$herite = $cnv->herite ; 

		if ($cnv->commentaire != null){
		$commentaire = $cnv->commentaire ; 
		}
		else{
		$commentaire = "''";
		}
		$classification = $cnv->classification ; 

		include ("connexion.php");
	
				$rqt3 = "SELECT * FROM puce
				WHERE idpuce='$puce'";
				$res3 = mysql_query($rqt3);

		while ($hg=mysql_fetch_object($res3))
				{
				$Genome = $hg->versionhg;
				$iddossier = $hg->iddossier;
				}

////////////////////////////////////////////////////////////////////////////////////////////////////		
		if ($Genome == 'hg18') {

		$GenomeConvert = 'hg19';
		$convertBaseB = exec("perl script_perl/liftover.pl chr$chro $baseB script_perl/hg18ToHg19.over.chain ; echo $?");
		$convertBaseH = exec("perl script_perl/liftover.pl chr$chro $baseH script_perl/hg18ToHg19.over.chain ; echo $?");
		}else{
		$GenomeConvert = 'hg18';
		$convertBaseB = exec("perl script_perl/liftover.pl chr$chro $baseB script_perl/hg19ToHg18.over.chain ; echo $?");
		$convertBaseH = exec("perl script_perl/liftover.pl chr$chro $baseH script_perl/hg19ToHg18.over.chain ; echo $?");
		}
/////////////////////////////////////////////////////////////////////////////////////////////////////
		  $convertBaseB=substr($convertBaseB, 0, -1);
		  $convertBaseH=substr($convertBaseH, 0, -1);

		if (($convertBaseB != -1) && ($convertBaseH != -1 )){
			if ($Genome == 'hg18') {
		$retour = exec("perl script_perl/script_insertion_cnv.pl $iddossier $puce $chro $baseB $baseH $nbcopie $log2ratio $Genome $sondedebut $sondefin $verifie $methodeverification $herite \"$commentaire\" $classification -1 ; echo $?");
		$retour2 = exec("perl script_perl/script_insertion_cnv.pl $iddossier $puce $chro $convertBaseB $convertBaseH $nbcopie $log2ratio $GenomeConvert $sondedebut $sondefin $verifie $methodeverification $herite \"$commentaire\" $classification ".substr($retour, 1)." ; echo $?");
		}else{
		$retour2 = exec("perl script_perl/script_insertion_cnv.pl $iddossier $puce $chro $convertBaseB $convertBaseH $nbcopie $log2ratio $GenomeConvert $sondedebut $sondefin $verifie $methodeverification $herite \"$commentaire\" $classification -1 ; echo $?");
		$retour = exec("perl script_perl/script_insertion_cnv.pl $iddossier $puce $chro $baseB $baseH $nbcopie $log2ratio $Genome $sondedebut $sondefin $verifie $methodeverification $herite \"$commentaire\" $classification ".substr($retour2, 1)." ; echo $?");
			}
		}
	
		else{
		echo "la localisation du CNV ne peut pas être correcte";
		}


		if (isset($retour)){
			
			$existe = substr($retour, 0, 1);
			$IdAno = substr($retour, 1);
			$existe2 = substr($retour2, 0, 1);
			$IdAno2 = substr($retour2, 1);
			$IdAno = substr($IdAno, 0, -1);
			$IdAno2 = substr($IdAno2, 0, -1);
			
		}

		//echo $retour."lll".$retour2;
///////////////////////////////////////////////////////////////////////////////////



	if(($existe==0)&&($existe2==0)){
		// le cnv existe
	echo "CNV inséré/";
	$ctrl=0;
		
	}elseif(($existe==1)&&($existe2==1)){
		//le cnv existe et est deja associé au dossier
		echo "CNV déjà inséré avec le nom $IdAno2 et associé au dossier N°$iddossier/";

        $ctrl=0;//permet de stocker le fait que la requête a abouti
	}elseif(($existe==2)&&($existe2==2)){
		//le cnv existe et est deja associé au patient
	echo "CNV déjà inséré avec l'ID N°$IdAno2 et associé au dossier N°$iddossier";
               
	}else{
	echo"problème : insertion impossible... Merci de recommencer";
		
	}
     
	

	// Si le CNV n'est pas encore associé au patient
	if ((($existe==0)&&($existe2==0)) ||(($existe==1)&&($existe2==1))){
		//insertion dans la table cnv_possede

	$requete = "INSERT INTO cnv_$Genome(idcnv, idpuce, positiondebut, positionfin, chromosome, sondedebut, sondefin, log2ratio, nbcopie ,verifie, methodeverification, herite, commentaire, classification, iddossier) 
	VALUES ($IdAno, $puce, $baseB, $baseH,$chro, $sondedebut, $sondefin, $log2ratio, $nbcopie , $verifie, $methodeverification, $herite, '$commentaire', $classification, $iddossier);";
				

	$requete2 = "INSERT INTO cnv_$GenomeConvert(idcnv, idpuce, positiondebut, positionfin, chromosome, sondedebut, sondefin, log2ratio, nbcopie ,verifie, methodeverification, herite, commentaire, classification, iddossier) 
	VALUES ($IdAno2, $puce, $convertBaseB, $convertBaseH,$chro, $sondedebut, $sondefin, $log2ratio, $nbcopie , $verifie, $methodeverification, $herite, '$commentaire', $classification, $iddossier);";

//echo $requete;

//echo $requete2;
		if (($result = mysql_query($requete))&&($result2 = mysql_query($requete2))){
		
				echo "CNV assigné au patient";
			
            
  				
		}else{
			if($ctrl==0){

			echo "problème : insertion impossible... Merci de recommencer";
				
                              
		}
	}
}


////////////////////////////////////////////////////////////////////////	
  	  	
		return $message;
	}//ajouter_CNV
	
/////////////////////////////////////////////////////////////////////////////////////////
	
	public function modifiersss_cnvdddd($cnv)
	{
		$message = null;
		$query = "UPDATE cnv SET ";
		if ($cnv->positiondebut != null){
			$query.= "positiondebut = :positiondebut , ";
		}
		if ($cnv->positionfin != null){
			$query.= "positionfin = :positionfin , ";
		}
		if ($cnv->chromosome != null){
			$query.= "chromosome = :chromosome , ";
		}
		if ($cnv->sondedebut != null){
			$query.= "sondedebut = :sondedebut , ";
		}
		if ($cnv->sondefin != null){
			$query.= "sondefin = :sondefin , ";
		}
		if ($cnv->log2ratio != null){
			$query.= "log2ratio = :log2ratio , ";
		}
		if ($cnv->nbcopie != null){
			$query.= "nbcopie = :nbcopie , ";
		}
		if ($cnv->verifie != null){
			$query.= "verifie = :verifie , ";
		}
		if ($cnv->methodeverification != null){
			$query.= "methodeverification = :methodeverification , ";
		}
		if ($cnv->herite != null){
			$query.= "herite = :herite , ";
		}
		if ($cnv->commentaire != null){
			$query.= "commentaire = :commentaire , ";
		}
		if ($cnv->classification != null){
			$query.= "classification = :classification , ";
		}
		$query = substr($query, 0,-2);
		$query = $query." WHERE idcnv= :idcnv";
		$requette_preparee = $this->connexion->prepare($query);
		if ($cnv->positiondebut != null){
		$requette_preparee->bindValue(":positiondebut" , $cnv->positiondebut, PDO::PARAM_INT);
		}
		if ($cnv->positionfin != null){
		$requette_preparee->bindValue(":positionfin" , $cnv->positionfin, PDO::PARAM_INT);
		}
		if ($cnv->chromosome != null){
		$requette_preparee->bindValue(":chromosome" , $cnv->chromosome, PDO::PARAM_STR);
		}
		if ($cnv->sondedebut != null){
			$requette_preparee->bindValue(":sondedebut" , $cnv->sondedebut, PDO::PARAM_STR);
		}
		if ($cnv->sondefin != null){
			$requette_preparee->bindValue(":sondefin" , $cnv->sondefin, PDO::PARAM_STR);
		};
		if ($cnv->log2ratio != null){
			$requette_preparee->bindValue(":log2ratio" , $cnv->log2ratio, PDO::PARAM_STR);
		};
		if ($cnv->nbcopie != null){
			$requette_preparee->bindValue(":nbcopie" , $cnv->nbcopie, PDO::PARAM_INT);
		}
			
		if ($cnv->verifie != null){
			$requette_preparee->bindValue(":verifie" , $cnv->verifie, PDO::PARAM_INT);
		}
		if ($cnv->methodeverification != null){
			$requette_preparee->bindValue(":methodeverification" , $cnv->methodeverification, PDO::PARAM_INT);
		}
		if ($cnv->herite != null){
			$requette_preparee->bindValue(":herite" , $cnv->herite, PDO::PARAM_INT);
		}
		if ($cnv->commentaire != null){
			$requette_preparee->bindValue(":commentaire" , $cnv->commentaire, PDO::PARAM_STR);
		}
		if ($cnv->classification != null){
			$requette_preparee->bindValue(":classification" , $cnv->classification, PDO::PARAM_INT);
		}
		$requette_preparee->bindValue(":idcnv" , $cnv->idcnv, PDO::PARAM_INT);
		$t = $requette_preparee->execute();
	
		if(!$t)
		{
			
			$taberr = $requette_preparee->errorInfo();
			$message = "Erreur numéro :".$taberr[0]."(".$taberr[1]."):".$taberr[2];
			
	
		}
		return $message;
	}





public function modifier_cnv($cnv)
	{


		$message = null;
		$cnv1 = $cnv->idcnv;

//echo $cnv1;

		$id_cnv18 = "CNV_".$cnv1;
		$id_cnv19 = "CNV_".$cnv1."0";
		$ReqSuppCNV="SELECT id FROM base_hg.hg18_name where name like '".$id_cnv18."'";
		$ReqSuppCNV2="SELECT id FROM base_hg.hg19_name where name like '".$id_cnv19."'";

//echo $ReqSuppCNV;
////////////////////////////////////////////////////////////////////////////////////////////////
		
////////////////////////////////////////////////////////////////////////////////////////////////
	include ("connexion.php");
		$puce = $cnv->idpuce;
				$rqt3 = "SELECT * FROM puce
				WHERE idpuce='$puce'";
				$res3 = mysql_query($rqt3);
		
		while ($hg=mysql_fetch_object($res3))
				{
				$Genome = $hg->versionhg;
				$iddossier = $hg->iddossier;
				}

			  
			  CNVDAO::ajouter_cnv($cnv);
/////////////////////////////////////////////////////////////////////////////////////////////			
	// Exécution des requêtes
	if (($result = mysql_query($ReqSuppCNV)) && ($result2 = mysql_query($ReqSuppCNV2))){
		while($row = mysql_fetch_object($result)){
			$id_cnv = $row->id;
//echo $id_cnv;
			$retour = exec("perl script_perl/script_suppression_CNV.pl hg18 $id_cnv");
			//<!-- 0006_10/01/2012 : début -->
			$ReqSuppLien = "DELETE FROM bdacpa.cnv_hg18 WHERE iddossier = $iddossier and idcnv = $id_cnv";
			//<!-- 0006_10/01/2012 : fin -->
		}
		while($row = mysql_fetch_object($result2)){
			$id_cnv = $row->id;
echo $id_cnv;
			$retour = exec("perl script_perl/script_suppression_CNV.pl hg19 $id_cnv");
			//<!-- 0006_10/01/2012 : début -->
			$ReqSuppLien2 = "DELETE FROM bdacpa.cnv_hg19 WHERE iddossier = $iddossier and idcnv = $id_cnv";
			//<!-- 0006_10/01/2012 : fin -->
		}
		if (($result = mysql_query($ReqSuppLien)) && ($result2 = mysql_query($ReqSuppLien2))){

		

		echo "Le CNV du dossier $iddossier a été supprimé";

		}else {
echo "Echec durant la suppression du CNV Veuillez recommencer";

		}
		}

else{
echo "Echec durant la suppression du CNV Veuillez recommencer";

}
	return $message;
}


} // CNVDAO



?>
